package com.jhs.kjs.handler;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.jhs.kjs.dto.SmsDTO;

public class MySQLAccess {
	private static final String LOCAL_DB = "jdbc:mysql://14.63.172.121:3306/aldang?user=root&password=snfldb~!90";
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;

	public int readDataBase(String id, String pw) throws Exception {
		int authorities = 0;
		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connect = DriverManager
					.getConnection(LOCAL_DB);

			// Statements allow to issue SQL queries to the database
			statement = connect.createStatement();
			// Result set get the result of the SQL query

			// PreparedStatements can use variables and are more efficient
			preparedStatement = connect
					.prepareStatement("SELECT level FROM USER WHERE userId=? AND userPw=HEX(AES_ENCRYPT(?, MD5('aldang+t_system+enc_k')))");
			// "myuser, webpage, datum, summary, COMMENTS from FEEDBACK.COMMENTS");
			// Parameters start with 1
			preparedStatement.setString(1, id);
			preparedStatement.setString(2, id+pw);
			System.out.println(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			
			
			if (resultSet == null)
				System.out.println("resultSet = null");
			if (resultSet.next()) {
				authorities = Integer.parseInt(resultSet.getObject(1)
						.toString());
			}

		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}
		return authorities;
	}

	public static int NULL_MSG = 0;

	public String sendSMSParse(String msg, String sender, String reciever)
			throws Exception {
		if (msg == null)
			throw new Exception("메시지가 없당께요");
		sender = sender.replaceAll("-", "");
		reciever = reciever.replaceAll("-", "");
		msg = msg.replaceAll("\n", "\r\n");

		System.out.println("msg=" + msg);
		System.out.println("sender=" + sender);
		System.out.println("reciever=" + reciever);

		return "성공적으로 요청하였습니다.";
	}

	private void sendSMS(String msg, String sender, String reciever)
			throws Exception {
		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connect = DriverManager
					.getConnection("jdbc:mysql://dbs.qro.co.kr:3306/imds?"
							+ "user=emma&password=emma0421");

			// Statements allow to issue SQL queries to the database
			statement = connect.createStatement();

			// Result set get the result of the SQL query

			// PreparedStatements can use variables and are more efficient

			StringBuilder sb = new StringBuilder();
			sb.append("INSERT INTO em_smt_tran ");
			sb.append("            (date_client_req, ");
			sb.append("             content, ");
			sb.append("             callback, ");
			sb.append("             service_type, ");
			sb.append("             broadcast_yn, ");
			sb.append("             msg_status, ");
			sb.append("             recipient_num) ");
			sb.append("VALUES     (Sysdate(), ");
			sb.append("            ?, ");
			sb.append("            ?, ");
			sb.append("            '0', ");
			sb.append("            'N', ");
			sb.append("            '1', ");
			sb.append("            ?)");

			System.out.println(sb.toString());
			preparedStatement = connect.prepareStatement(sb.toString());
			// "myuser, webpage, datum, summary, COMMENTS from FEEDBACK.COMMENTS");
			// Parameters start with 1
			preparedStatement.setString(1, msg);
			preparedStatement.setString(2, sender);
			preparedStatement.setString(3, reciever);

			resultSet = preparedStatement.executeQuery();
			System.out.println(resultSet.getStatement());
			//
		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}
	}

	
	public List<SmsDTO> getSentSMSList(String reciever) throws Exception {
		reciever = reciever.replaceAll("-", "").replaceAll(" ", "");
		return getSentSMS(reciever);
	}
	
	private List<SmsDTO> getSentSMS(String reciever) throws Exception {
		List<SmsDTO> list = null;
		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connect = DriverManager
					.getConnection("jdbc:mysql://dbs.qro.co.kr:3306/imds?"
							+ "user=emma&password=emma0421");

			// Statements allow to issue SQL queries to the database
			statement = connect.createStatement();

			// Result set get the result of the SQL query

			// PreparedStatements can use variables and are more efficient

			StringBuilder sb = new StringBuilder();
			sb.append("SELECT date_mt_sent, ");
			sb.append("       content, ");
			sb.append("       date_client_req, ");
			sb.append("       recipient_num, ");
			sb.append("       callback, ");
			sb.append("       msg_status ");
			sb.append("FROM   em_smt_tran ");
			sb.append("WHERE  recipient_num = ?");

			preparedStatement = connect.prepareStatement(sb.toString());
			// "myuser, webpage, datum, summary, COMMENTS from FEEDBACK.COMMENTS");
			// Parameters start with 1
			preparedStatement.setString(1, reciever);

			ResultSet resultSet = preparedStatement.executeQuery();

			System.out.println(resultSet.getStatement());
			
			list = new ArrayList<SmsDTO>(); 
			while(resultSet.next()){
				SmsDTO sms = new SmsDTO();
				
				try{sms.setDate_mt_sent(resultSet.getDate("date_mt_sent"));}catch(Exception e){};
				try{sms.setContent(resultSet.getString("content"));}catch(Exception e){};
				try{sms.setDate_client_req(resultSet.getDate("date_client_req"));}catch(Exception e){};
				try{sms.setRecipient_num(resultSet.getString("recipient_num"));}catch(Exception e){};
				try{sms.setCallback(resultSet.getString("callback"));}catch(Exception e){};
				try{sms.setMsg_status(resultSet.getString("msg_status"));}catch(Exception e){};
				
				list.add(sms);
			}
			
		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}
		
		return list;
	}

	private void writeMetaData(ResultSet resultSet) throws SQLException {
		// Now get some metadata from the database
		// Result set get the result of the SQL query

		System.out.println("The columns in the table are: ");

		System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
		for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
			System.out.println("Column " + i + " "
					+ resultSet.getMetaData().getColumnName(i));
		}
	}

	private void writeResultSet(ResultSet resultSet) throws SQLException {
		// ResultSet is initially before the first data set
		resultSet.getInt("");
	}

	// You need to close the resultSet
	private void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}

}